# This file is used to create a singleton class for database connection and cursor.

import psycopg2
import psycopg2.pool
import psycopg2.extras
import os
from setting import *

import logging

logging.basicConfig(level = logging.INFO,format = '%(asctime)s - %(name)s - %(levelname)s - %(message)s')

logger = logging.getLogger(__name__)

class PostgreSingleton:
    _instance = None

    def __new__(cls, *args, **kwargs):
        if not cls._instance:
            cls._instance = super().__new__(cls)
        return cls._instance

    def __init__(self) -> None:
        # 创建一个进程池
        self.db_pool = psycopg2.pool.SimpleConnectionPool(
            minconn=1,
            maxconn=100,
            host=DATABASE_HOST,
            port=DATABASE_PORT,
            database=DATABASE_NAME,
            user=DATABASE_USER,
            password=DATABASE_PSWD
        )
        # 实例化时执行 SQL 文件
        self.init_db_from_script()

    def init_db_from_script(self):
        script_path = "database/init_db.sql"  
        logger.info(os.path.exists(script_path))
        if os.path.exists(script_path):
            logger.info("init database from script")
            with open(script_path, 'r', encoding='utf-8') as file:
                sql_script = file.read()
                with self.db_pool.getconn() as conn:
                    with conn.cursor() as cur:
                        cur.execute(sql_script)
                        conn.commit()  # 提交事务




if __name__ == "__main__":

    #调用示例

    ps = PostgreSingleton()

    with ps.db_pool.getconn() as conn:
        with conn.cursor() as cur:
            cur.execute("select * from cabinet where mac=%s", ("620790426a0e",))  # 执行 SQL 查询
            rows = cur.fetchone()  # 检索查询结果

    logger.info(rows)